﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace Token.Piker.DAL
{
	/// <summary>
	/// 数据访问类:PhotoDAL
	/// </summary>
	public partial class PhotoDAL
	{
		public PhotoDAL()
		{}
		#region  BasicMethod

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("ID", "t_Photo"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int ID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from t_Photo");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
			parameters[0].Value = ID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(Token.Piker.Model.Photo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into t_Photo(");
			strSql.Append("UserID,PhotoName,UploadTime,Descrip,Position,Province,City,SourceUrl,CompressUrl_1,CompressUrl_2,CompressUrl_3,CompressUrl_4,Album,LikeCount,Make,Model,Software,ExposureTime,FNumber,ExposureProgram,ISO,ExposureBiasValue,Lightsource,MeteringMode,ExifVersionExif,Flag)");
			strSql.Append(" values (");
			strSql.Append("@UserID,@PhotoName,@UploadTime,@Descrip,@Position,@Province,@City,@SourceUrl,@CompressUrl_1,@CompressUrl_2,@CompressUrl_3,@CompressUrl_4,@Album,@LikeCount,@Make,@Model,@Software,@ExposureTime,@FNumber,@ExposureProgram,@ISO,@ExposureBiasValue,@Lightsource,@MeteringMode,@ExifVersionExif,@Flag)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@PhotoName", SqlDbType.VarChar,50),
					new SqlParameter("@UploadTime", SqlDbType.DateTime),
					new SqlParameter("@Descrip", SqlDbType.VarChar,1280),
					new SqlParameter("@Position", SqlDbType.VarChar,1280),
					new SqlParameter("@Province", SqlDbType.VarChar,1280),
					new SqlParameter("@City", SqlDbType.VarChar,1280),
					new SqlParameter("@SourceUrl", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_1", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_2", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_3", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_4", SqlDbType.VarChar,255),
					new SqlParameter("@Album", SqlDbType.Int,4),
					new SqlParameter("@LikeCount", SqlDbType.Int,4),
					new SqlParameter("@Make", SqlDbType.VarChar,400),
					new SqlParameter("@Model", SqlDbType.VarChar,400),
					new SqlParameter("@Software", SqlDbType.VarChar,400),
					new SqlParameter("@ExposureTime", SqlDbType.VarChar,400),
					new SqlParameter("@FNumber", SqlDbType.VarChar,400),
					new SqlParameter("@ExposureProgram", SqlDbType.VarChar,400),
					new SqlParameter("@ISO", SqlDbType.VarChar,400),
					new SqlParameter("@ExposureBiasValue", SqlDbType.VarChar,400),
					new SqlParameter("@Lightsource", SqlDbType.VarChar,400),
					new SqlParameter("@MeteringMode", SqlDbType.VarChar,400),
					new SqlParameter("@ExifVersionExif", SqlDbType.VarChar,400),
					new SqlParameter("@Flag", SqlDbType.Int,4)};
			parameters[0].Value = model.UserID;
			parameters[1].Value = model.PhotoName;
			parameters[2].Value = model.UploadTime;
			parameters[3].Value = model.Descrip;
			parameters[4].Value = model.Position;
			parameters[5].Value = model.Province;
			parameters[6].Value = model.City;
			parameters[7].Value = model.SourceUrl;
			parameters[8].Value = model.CompressUrl_1;
			parameters[9].Value = model.CompressUrl_2;
			parameters[10].Value = model.CompressUrl_3;
			parameters[11].Value = model.CompressUrl_4;
			parameters[12].Value = model.Album;
			parameters[13].Value = model.LikeCount;
			parameters[14].Value = model.Make;
			parameters[15].Value = model.Model;
			parameters[16].Value = model.Software;
			parameters[17].Value = model.ExposureTime;
			parameters[18].Value = model.FNumber;
			parameters[19].Value = model.ExposureProgram;
			parameters[20].Value = model.ISO;
			parameters[21].Value = model.ExposureBiasValue;
			parameters[22].Value = model.Lightsource;
			parameters[23].Value = model.MeteringMode;
			parameters[24].Value = model.ExifVersionExif;
			parameters[25].Value = model.Flag;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(Token.Piker.Model.Photo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update t_Photo set ");
			strSql.Append("UserID=@UserID,");
			strSql.Append("PhotoName=@PhotoName,");
			strSql.Append("UploadTime=@UploadTime,");
			strSql.Append("Descrip=@Descrip,");
			strSql.Append("Position=@Position,");
			strSql.Append("Province=@Province,");
			strSql.Append("City=@City,");
			strSql.Append("SourceUrl=@SourceUrl,");
			strSql.Append("CompressUrl_1=@CompressUrl_1,");
			strSql.Append("CompressUrl_2=@CompressUrl_2,");
			strSql.Append("CompressUrl_3=@CompressUrl_3,");
			strSql.Append("CompressUrl_4=@CompressUrl_4,");
			strSql.Append("Album=@Album,");
			strSql.Append("LikeCount=@LikeCount,");
			strSql.Append("Make=@Make,");
			strSql.Append("Model=@Model,");
			strSql.Append("Software=@Software,");
			strSql.Append("ExposureTime=@ExposureTime,");
			strSql.Append("FNumber=@FNumber,");
			strSql.Append("ExposureProgram=@ExposureProgram,");
			strSql.Append("ISO=@ISO,");
			strSql.Append("ExposureBiasValue=@ExposureBiasValue,");
			strSql.Append("Lightsource=@Lightsource,");
			strSql.Append("MeteringMode=@MeteringMode,");
			strSql.Append("ExifVersionExif=@ExifVersionExif,");
			strSql.Append("Flag=@Flag");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@PhotoName", SqlDbType.VarChar,50),
					new SqlParameter("@UploadTime", SqlDbType.DateTime),
					new SqlParameter("@Descrip", SqlDbType.VarChar,1280),
					new SqlParameter("@Position", SqlDbType.VarChar,1280),
					new SqlParameter("@Province", SqlDbType.VarChar,1280),
					new SqlParameter("@City", SqlDbType.VarChar,1280),
					new SqlParameter("@SourceUrl", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_1", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_2", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_3", SqlDbType.VarChar,255),
					new SqlParameter("@CompressUrl_4", SqlDbType.VarChar,255),
					new SqlParameter("@Album", SqlDbType.Int,4),
					new SqlParameter("@LikeCount", SqlDbType.Int,4),
					new SqlParameter("@Make", SqlDbType.VarChar,400),
					new SqlParameter("@Model", SqlDbType.VarChar,400),
					new SqlParameter("@Software", SqlDbType.VarChar,400),
					new SqlParameter("@ExposureTime", SqlDbType.VarChar,400),
					new SqlParameter("@FNumber", SqlDbType.VarChar,400),
					new SqlParameter("@ExposureProgram", SqlDbType.VarChar,400),
					new SqlParameter("@ISO", SqlDbType.VarChar,400),
					new SqlParameter("@ExposureBiasValue", SqlDbType.VarChar,400),
					new SqlParameter("@Lightsource", SqlDbType.VarChar,400),
					new SqlParameter("@MeteringMode", SqlDbType.VarChar,400),
					new SqlParameter("@ExifVersionExif", SqlDbType.VarChar,400),
					new SqlParameter("@Flag", SqlDbType.Int,4),
					new SqlParameter("@ID", SqlDbType.Int,4)};
			parameters[0].Value = model.UserID;
			parameters[1].Value = model.PhotoName;
			parameters[2].Value = model.UploadTime;
			parameters[3].Value = model.Descrip;
			parameters[4].Value = model.Position;
			parameters[5].Value = model.Province;
			parameters[6].Value = model.City;
			parameters[7].Value = model.SourceUrl;
			parameters[8].Value = model.CompressUrl_1;
			parameters[9].Value = model.CompressUrl_2;
			parameters[10].Value = model.CompressUrl_3;
			parameters[11].Value = model.CompressUrl_4;
			parameters[12].Value = model.Album;
			parameters[13].Value = model.LikeCount;
			parameters[14].Value = model.Make;
			parameters[15].Value = model.Model;
			parameters[16].Value = model.Software;
			parameters[17].Value = model.ExposureTime;
			parameters[18].Value = model.FNumber;
			parameters[19].Value = model.ExposureProgram;
			parameters[20].Value = model.ISO;
			parameters[21].Value = model.ExposureBiasValue;
			parameters[22].Value = model.Lightsource;
			parameters[23].Value = model.MeteringMode;
			parameters[24].Value = model.ExifVersionExif;
			parameters[25].Value = model.Flag;
			parameters[26].Value = model.ID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from t_Photo ");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
			parameters[0].Value = ID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string IDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from t_Photo ");
			strSql.Append(" where ID in ("+IDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Token.Piker.Model.Photo GetModel(int ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 ID,UserID,PhotoName,UploadTime,Descrip,Position,Province,City,SourceUrl,CompressUrl_1,CompressUrl_2,CompressUrl_3,CompressUrl_4,Album,LikeCount,Make,Model,Software,ExposureTime,FNumber,ExposureProgram,ISO,ExposureBiasValue,Lightsource,MeteringMode,ExifVersionExif,Flag from t_Photo ");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
			parameters[0].Value = ID;

			Token.Piker.Model.Photo model=new Token.Piker.Model.Photo();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Token.Piker.Model.Photo DataRowToModel(DataRow row)
		{
			Token.Piker.Model.Photo model=new Token.Piker.Model.Photo();
			if (row != null)
			{
				if(row["ID"]!=null && row["ID"].ToString()!="")
				{
					model.ID=int.Parse(row["ID"].ToString());
				}
				if(row["UserID"]!=null && row["UserID"].ToString()!="")
				{
					model.UserID=int.Parse(row["UserID"].ToString());
				}
				if(row["PhotoName"]!=null)
				{
					model.PhotoName=row["PhotoName"].ToString();
				}
				if(row["UploadTime"]!=null && row["UploadTime"].ToString()!="")
				{
					model.UploadTime=DateTime.Parse(row["UploadTime"].ToString());
				}
				if(row["Descrip"]!=null)
				{
					model.Descrip=row["Descrip"].ToString();
				}
				if(row["Position"]!=null)
				{
					model.Position=row["Position"].ToString();
				}
				if(row["Province"]!=null)
				{
					model.Province=row["Province"].ToString();
				}
				if(row["City"]!=null)
				{
					model.City=row["City"].ToString();
				}
				if(row["SourceUrl"]!=null)
				{
					model.SourceUrl=row["SourceUrl"].ToString();
				}
				if(row["CompressUrl_1"]!=null)
				{
					model.CompressUrl_1=row["CompressUrl_1"].ToString();
				}
				if(row["CompressUrl_2"]!=null)
				{
					model.CompressUrl_2=row["CompressUrl_2"].ToString();
				}
				if(row["CompressUrl_3"]!=null)
				{
					model.CompressUrl_3=row["CompressUrl_3"].ToString();
				}
				if(row["CompressUrl_4"]!=null)
				{
					model.CompressUrl_4=row["CompressUrl_4"].ToString();
				}
				if(row["Album"]!=null && row["Album"].ToString()!="")
				{
					model.Album=int.Parse(row["Album"].ToString());
				}
				if(row["LikeCount"]!=null && row["LikeCount"].ToString()!="")
				{
					model.LikeCount=int.Parse(row["LikeCount"].ToString());
				}
				if(row["Make"]!=null)
				{
					model.Make=row["Make"].ToString();
				}
				if(row["Model"]!=null)
				{
					model.Model=row["Model"].ToString();
				}
				if(row["Software"]!=null)
				{
					model.Software=row["Software"].ToString();
				}
				if(row["ExposureTime"]!=null)
				{
					model.ExposureTime=row["ExposureTime"].ToString();
				}
				if(row["FNumber"]!=null)
				{
					model.FNumber=row["FNumber"].ToString();
				}
				if(row["ExposureProgram"]!=null)
				{
					model.ExposureProgram=row["ExposureProgram"].ToString();
				}
				if(row["ISO"]!=null)
				{
					model.ISO=row["ISO"].ToString();
				}
				if(row["ExposureBiasValue"]!=null)
				{
					model.ExposureBiasValue=row["ExposureBiasValue"].ToString();
				}
				if(row["Lightsource"]!=null)
				{
					model.Lightsource=row["Lightsource"].ToString();
				}
				if(row["MeteringMode"]!=null)
				{
					model.MeteringMode=row["MeteringMode"].ToString();
				}
				if(row["ExifVersionExif"]!=null)
				{
					model.ExifVersionExif=row["ExifVersionExif"].ToString();
				}
				if(row["Flag"]!=null && row["Flag"].ToString()!="")
				{
					model.Flag=int.Parse(row["Flag"].ToString());
				}
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ID,UserID,PhotoName,UploadTime,Descrip,Position,Province,City,SourceUrl,CompressUrl_1,CompressUrl_2,CompressUrl_3,CompressUrl_4,Album,LikeCount,Make,Model,Software,ExposureTime,FNumber,ExposureProgram,ISO,ExposureBiasValue,Lightsource,MeteringMode,ExifVersionExif,Flag ");
			strSql.Append(" FROM t_Photo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" ID,UserID,PhotoName,UploadTime,Descrip,Position,Province,City,SourceUrl,CompressUrl_1,CompressUrl_2,CompressUrl_3,CompressUrl_4,Album,LikeCount,Make,Model,Software,ExposureTime,FNumber,ExposureProgram,ISO,ExposureBiasValue,Lightsource,MeteringMode,ExifVersionExif,Flag ");
			strSql.Append(" FROM t_Photo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM t_Photo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.ID desc");
			}
			strSql.Append(")AS Row, T.*  from t_Photo T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "t_Photo";
			parameters[1].Value = "ID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  BasicMethod
		#region  ExtensionMethod
        /// <summary>
        /// 取得首页最新上传的单张和组图的图片的列表
        /// 逻辑较为复杂
        /// 请勿随意更改
        /// </summary>
        /// <returns></returns>
        public DataSet GetHomePhotoList()
        {
            string sql = @"SELECT TOP 27 * FROM(
	                        SELECT * FROM 
	                        (
		                        SELECT TOP 27 t_Photo.ID,UserName,UploadTime,Descrip,Position,Album,t_Photo.Flag
		                        FROM t_Photo INNER JOIN t_User
		                        ON t_Photo.UserID = t_User.ID
		                        WHERE Album IS NULL
		                        AND t_Photo.Flag = '0'
		                        ORDER BY UploadTime DESC
	                        ) B
	                        UNION
	                        SELECT * FROM
	                        (
		                        SELECT Top 27 t_Photo.ID,UserName,UploadTime,Descrip,Position,Album,t_Photo.Flag
		                        FROM t_Photo INNER JOIN t_User
		                        ON t_Photo.UserID = t_User.ID
		                        AND UploadTime IN
		                        (
			                        SELECT TOP 27 MAX(UploadTime) AS UploadTime FROM
			                        (
				                        SELECT Album,UploadTime FROM t_Photo 
				                        WHERE Flag = '0'
				                        AND Album IN
				                        (
					                         SELECT TOP 27 ID FROM t_Album 
					                         ORDER BY CreateTime DESC
				                        )
			                        ) A
			                        GROUP BY A.Album
		                        ) 
		                        ORDER BY UploadTime DESC
	                        )BB
                        ) TT
                        ORDER BY TT.UploadTime DESC";
            return DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 根据图片的ID取得上传者的用户名以及图片标签信息
        /// </summary>
        /// <param name="id">图片ID</param>
        /// <returns></returns>
        public DataSet GetUserAndTagNameOfPhoto(int id)
        {
            SqlParameter param = new SqlParameter("@PhotoID", id);
            string sql = @"SELECT UserName,TagName FROM t_Photo 
                        INNER JOIN t_User 
                        ON t_Photo.UserID = t_User.ID
                        INNER JOIN r_PhotoTag 
                        ON r_PhotoTag.PID = t_Photo.ID
                        INNER JOIN t_Tag 
                        ON r_PhotoTag.TID = t_Tag.ID
                        WHERE t_Photo.ID = @PhotoID";
            DataSet ds = DbHelperSQL.Query(sql, param);
            if (ds.Tables[0].Rows.Count == 2)
            {
                ds.Tables[0].Rows[0]["TagName"] = ds.Tables[0].Rows[0]["TagName"].ToString() +" "+ ds.Tables[0].Rows[1]["TagName"].ToString();
                ds.Tables[0].Rows.RemoveAt(1);
            }
            return ds;
        }

        /// <summary>
        /// 根据图片ID取得图片信息
        /// 针对首页而写
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetPhotoInfoByID(int id)
        {
            SqlParameter param = new SqlParameter("@PhotoID", id);
            string sql = @"SELECT UserName,Position,UploadTime,Descrip,TagName FROM t_Photo 
                            INNER JOIN t_User 
                            ON t_Photo.UserID = t_User.ID
                            INNER JOIN r_PhotoTag 
                            ON r_PhotoTag.PID = t_Photo.ID
                            INNER JOIN t_Tag 
                            ON r_PhotoTag.TID = t_Tag.ID
                            WHERE t_Photo.ID = @PhotoID";
            DataSet ds = DbHelperSQL.Query(sql, param);
            if (ds.Tables[0].Rows.Count == 2)
            {
                ds.Tables[0].Rows[0]["TagName"] = ds.Tables[0].Rows[0]["TagName"].ToString() + " " + ds.Tables[0].Rows[1]["TagName"].ToString();
                ds.Tables[0].Rows.RemoveAt(1);
            }
            return ds;
        }
		#endregion  ExtensionMethod
	}
}

